﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Dapper;
using BCCommon;
using BCCommon.Enums;
using BCEntity.TMS.NotificationMessageRecord;

namespace BCData.TMS.NotificationMessageRecord
{
    public class NotificationMessageRecordData : INotificationMessageRecordData
    {
        private const string QUERY = @"SELECT NotificationMessageId,NotificationTitle,NotificationContent,NotificationType,NotificationParameters,AdminId,CreateTime,RelatedRecordId,IsRead,ReadTime,IsHandled,HandledTime,HandledRemark,UserId,BusinessType 
                                       FROM NotificationMessageRecord";
        private const string QUERY_VIEW = @"SELECT A.NotificationMessageId,A.NotificationTitle,A.NotificationContent,A.NotificationType,A.NotificationParameters,A.AdminId,A.CreateTime,A.RelatedRecordId,A.IsRead,A.ReadTime,A.IsHandled,A.HandledTime,A.HandledRemark,B.FullName ,A.BusinessType 
                                            FROM NotificationMessageRecord A LEFT JOIN Admin B ON A.AdminId = B.AdminId";

        private readonly IDatabaseContext database;
        public NotificationMessageRecordData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<NotificationMessageRecordEntity> GetNotificationMessageRecordAsync(long notificationMessageId)
        {
            var sql = typeof(NotificationMessageRecordEntity).GetSelectSqlByPrimaryKeys();

            return await database.Slave.QueryFirstOrDefaultAsync<NotificationMessageRecordEntity>(sql, new { notificationMessageId });
        }

        public async Task<(IEnumerable<NotificationMessageRecordEntity>, int)> GetAdminMessagesAsync(long adminId, bool? isRead, NotificationMessageRecordType? notificationMessageRecordType, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add("A.AdminId=?AdminId");
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);

            if (isRead != null)
            {
                wheres.Add("A.IsRead=?IsRead");
                parameters.Add("?IsRead", isRead, DbType.Boolean);
            }

            if (notificationMessageRecordType != null)
            {
                wheres.Add("A.NotificationType=?NotificationType");
                parameters.Add("?NotificationType", notificationMessageRecordType.ToString(), DbType.String);
            }

            string sql = string.Format(@"{0} {1} ORDER BY A.CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM NotificationMessageRecord A {1}",
                QUERY_VIEW,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex - 1) * pageSize, pageSize);

            return await database.Slave.QueryMultipleAsync<NotificationMessageRecordEntity, int>(sql, parameters);
        }

        public async Task<(IEnumerable<NotificationMessageRecordEntity>, int)> GetUserMessagesAsync(long userId, bool? isRead, NotificationMessageRecordType? notificationMessageRecordType, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add("A.UserId=?UserId");
            parameters.Add("?UserId", userId, DbType.Int64, size: 20);

            if (isRead != null)
            {
                wheres.Add("A.IsRead=?IsRead");
                parameters.Add("?IsRead", isRead, DbType.Boolean);
            }

            if (notificationMessageRecordType != null)
            {
                wheres.Add("A.NotificationType=?NotificationType");
                parameters.Add("?NotificationType", notificationMessageRecordType.ToString(), DbType.String);
            }

            string sql = string.Format(@"SELECT A.NotificationMessageId,A.NotificationTitle,A.NotificationContent,A.NotificationType,A.NotificationParameters,A.AdminId,
                                         A.CreateTime,A.RelatedRecordId,A.IsRead,A.ReadTime,A.IsHandled,A.HandledTime,A.HandledRemark,B.UserName,A.BusinessType 
                                         FROM NotificationMessageRecord A 
                                         LEFT JOIN `User` B ON A.UserId = B.UserId {0} ORDER BY A.CreateTime DESC LIMIT {1},{2}; SELECT COUNT(1) FROM NotificationMessageRecord A {0}",
                                         wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                                         (pageIndex - 1) * pageSize, pageSize);

            return await database.Slave.QueryMultipleAsync<NotificationMessageRecordEntity, int>(sql, parameters);
        }

        public async Task<int> GetAdminUnreadCountAsync(long adminId)
        {
            const string sql = "SELECT COUNT(1) FROM NotificationMessageRecord WHERE AdminId = ?AdminId AND IsRead = 0 AND CreateTime >= ?CreateTime";

            return await database.Slave.ExecuteScalarAsync<int>(sql, new { adminId, CreateTime = DateTime.Today.AddMonths(-1) });
        }

        public async Task<int> GetUserUnreadCountAsync(long userId)
        {
            const string sql = "SELECT COUNT(1) FROM NotificationMessageRecord WHERE UserId = ?UserId AND IsRead = 0 AND CreateTime >= ?CreateTime";

            return await database.Slave.ExecuteScalarAsync<int>(sql, new { userId, CreateTime = DateTime.Today.AddMonths(-1) });
        }

        public async Task<IEnumerable<NotificationGroupMessageCountEntity>> GetAdminGroupUnreadCountAsync(long adminId)
        {
            const string sql = @"SELECT COUNT(1) AS NotificationMessageCount, NotificationType
FROM NotificationMessageRecord
WHERE AdminId = ?AdminId AND IsRead = 0 AND CreateTime >= ?CreateTime GROUP BY NotificationType";

            return await database.Slave.QueryAsync<NotificationGroupMessageCountEntity>(sql, new { adminId, CreateTime = DateTime.Today.AddMonths(-1) });
        }

        public async Task<IEnumerable<NotificationGroupMessageCountEntity>> GetUserGroupUnreadCountAsync(long userId)
        {
            const string sql = @"SELECT COUNT(1) AS NotificationMessageCount, NotificationType
FROM NotificationMessageRecord
WHERE UserId = ?UserId AND IsRead = 0 AND CreateTime >= ?CreateTime GROUP BY NotificationType";

            return await database.Slave.QueryAsync<NotificationGroupMessageCountEntity>(sql, new { userId, CreateTime = DateTime.Today.AddMonths(-1) });
        }

        public async Task<NotificationMessageRecordEntity> InsertAsync(NotificationMessageRecordEntity entity)
        {
            return await database.Master.InsertAsync(entity);
        }

        public async Task<int> InsertAllAsync(IEnumerable<NotificationMessageRecordEntity> entities)
        {
            return await database.Master.InsertAllAsync(entities);
        }




        public async Task<NotificationMessageRecordEntity> UpdateReadStatus(long notificationMessageId)
        {
            string sql = string.Format(@"UPDATE NotificationMessageRecord SET IsRead=?IsRead, ReadTime=?ReadTime
                                         WHERE NotificationMessageId=?NotificationMessageId;
                                         {0} WHERE NotificationMessageId=?NotificationMessageId", QUERY);

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsRead", true, DbType.Boolean);
            parameters.Add("?ReadTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?NotificationMessageId", notificationMessageId, DbType.Int64, size: 20);

            return await database.Master.QueryFirstOrDefaultAsync<NotificationMessageRecordEntity>(sql, parameters);
        }

        public async Task<bool> UpdateBatchIsRead(long adminId, NotificationMessageRecordType? notificationType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("AdminId=?AdminId");
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            if (notificationType.HasValue)
            {
                wheres.Add(string.Format("NotificationType='{0}'", notificationType.ToString()));
            }
            wheres.Add("IsRead=?IsRead");
            parameters.Add("?IsRead", false, DbType.Boolean);
            parameters.Add("?UpdateIsRead", true, DbType.Boolean);
            parameters.Add("?ReadTime", DateTime.Now, DbType.DateTime);
            string sql = string.Format(@"UPDATE NotificationMessageRecord SET
                                IsRead=?UpdateIsRead,
                                ReadTime=?ReadTime {0}",
                               wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await database.Master.ExecuteAsync(sql, parameters) > 0;
        }

        public async Task<NotificationMessageRecordEntity> UpdateIsHandled(long notificationMessageId, string handledRemark)
        {
            string sql = string.Format(@"UPDATE NotificationMessageRecord SET IsHandled=?IsHandled, HandledTime=?HandledTime,HandledRemark=?HandledRemark
                                         WHERE NotificationMessageId=?NotificationMessageId;
                                         {0} WHERE NotificationMessageId=?NotificationMessageId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsHandled", true, DbType.Boolean);
            parameters.Add("?HandledTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?HandledRemark", handledRemark, DbType.String);
            parameters.Add("?NotificationMessageId", notificationMessageId, DbType.Int64);
            return await database.Master.QueryFirstOrDefaultAsync<NotificationMessageRecordEntity>(sql, parameters);
        }
    }
}
